package com.wugui.datax.admin.tool.generate.sql;

import cn.hutool.core.util.StrUtil;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Lists;
import com.wugui.datax.admin.entity.JobDatasource;
import com.wugui.datax.admin.tool.SpringContextHolder;
import com.wugui.datax.admin.tool.database.DasColumn;
import com.wugui.datax.admin.tool.generate.dialect.DialectHelper;
import com.wugui.datax.admin.tool.generate.util.QueryBody;
import com.wugui.datax.admin.tool.meta.DatabaseInterface;
import com.wugui.datax.admin.tool.meta.DatabaseMetaFactory;
import com.wugui.datax.admin.util.JdbcConstants;
import com.wugui.datax.admin.util.JdbcUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 通用 Rdbms sql生成器
 */
public class CommonRdbmsGenerateSql implements GenerateSqlInterface {
    protected static final Logger logger = LoggerFactory.getLogger(CommonRdbmsGenerateSql.class);

    private DatabaseInterface sqlBuilder;
    private Connection connection;
    private DialectHelper dialectHelper;
    private String currentSchema;
    private String currentDatabase;

    List<DasColumn> dasColumns;

    /**
     * 初始化，获取表字段类型 metaData
     *
     * @param queryBody
     * @throws SQLException
     */
    public CommonRdbmsGenerateSql(QueryBody queryBody) throws SQLException {
        try {

            JobDatasource jobDatasource = queryBody.getDatasource();

            this.sqlBuilder = DatabaseMetaFactory.getByDbType(jobDatasource.getDatasource());
            String querySql = sqlBuilder.getSQLQueryFields(queryBody.getTableName());
            logger.info("querySql: {}", querySql);

            //获取所有字段
            DynamicRoutingDataSource ds = SpringContextHolder.getContext().getBean(DynamicRoutingDataSource.class);
            this.connection = ds.getConnection();

            this.currentSchema = getSchema(jobDatasource.getJdbcUsername());
            this.currentDatabase = jobDatasource.getDatasource();

            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(querySql);
            ResultSetMetaData metaData = resultSet.getMetaData();

            this.dasColumns = buildDasColumn(queryBody.getTableName(), metaData);
            statement.close();
            //数据库方言支持
            this.dialectHelper = new DialectHelper(jobDatasource, dasColumns);

        } catch (SQLException e) {
            logger.error("[getMetaData Exception] --> the exception message is:" + e.getMessage());
            throw e;
        }
    }

    //根据connection获取schema
    private String getSchema(String jdbcUsername) {
        String res = null;
        try {
            res = connection.getCatalog();
        } catch (SQLException e) {
            try {
                res = connection.getSchema();
            } catch (SQLException e1) {
                logger.error("[SQLException getSchema Exception] --> " + "the exception message is:" + e1.getMessage());
            }
            logger.error("[getSchema Exception] --> " + "the exception message is:" + e.getMessage());
        }
        // 如果res是null，则将用户名当作 schema
        if (StrUtil.isBlank(res) && StringUtils.isNotBlank(jdbcUsername)) {
            res = jdbcUsername.toUpperCase();
        }
        return res;
    }

    //构建DasColumn对象
    private List<DasColumn> buildDasColumn(String tableName, ResultSetMetaData metaData) {
        List<DasColumn> res = Lists.newArrayList();
        try {
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                DasColumn dasColumn = new DasColumn();
                dasColumn.setColumnClassName(metaData.getColumnClassName(i));
                dasColumn.setColumnTypeName(metaData.getColumnTypeName(i));
                dasColumn.setColumnName(metaData.getColumnName(i));
                dasColumn.setIsNull(metaData.isNullable(i));

                res.add(dasColumn);
            }

            Statement statement = connection.createStatement();

            if (currentDatabase.equals(JdbcConstants.MYSQL) || currentDatabase.equals(JdbcConstants.ORACLE)) {
                DatabaseMetaData databaseMetaData = connection.getMetaData();

                ResultSet resultSet = databaseMetaData.getPrimaryKeys(null, null, tableName);

                while (resultSet.next()) {
                    String name = resultSet.getString("COLUMN_NAME");
                    res.forEach(e -> {
                        if (e.getColumnName().equals(name)) {
                            e.setIsprimaryKey(true);

                        } else {
                            e.setIsprimaryKey(false);
                        }
                    });
                }

                res.forEach(e -> {
                    String sqlQueryComment = sqlBuilder.getSQLQueryComment(currentSchema, tableName, e.getColumnName());
                    //查询字段注释
                    try {
                        ResultSet resultSetComment = statement.executeQuery(sqlQueryComment);
                        while (resultSetComment.next()) {
                            e.setColumnComment(resultSetComment.getString(1));
                        }
                        JdbcUtils.close(resultSetComment);
                    } catch (SQLException e1) {
                        logger.error("[buildDasColumn executeQuery Exception] --> " + "the exception message is:" + e1.getMessage());
                    }
                });
            }

            JdbcUtils.close(statement);
        } catch (SQLException e) {
            logger.error("[buildDasColumn Exception] --> " + "the exception message is:" + e.getMessage());
        }
        return res;
    }

    /**
     * 拼接查询字段
     * <p>
     * 发现日期字段先按照UTC时间进行格式化
     */
    @Override
    public String exampleSelectColumns(QueryBody qb) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");

        List<String> fields = qb.getFields();

        if (fields == null) {
            List<String> collect = dasColumns.stream().map(DasColumn::getColumnName).collect(Collectors.toList());
            sql.append(StringUtils.join(collect, ","));
        } else {
            sql.append(StringUtils.join(fields, ","));
        }
        sql.append(" ");
        return sql.toString();
    }

    @Override
    public String fromTable(QueryBody qb) {
        StringBuilder sql = new StringBuilder();
        sql.append(" FROM ");
        sql.append(qb.getTableName());
        sql.append(" ");
        return sql.toString();
    }

    @Override
    public String exampleWhereClause(QueryBody qb) {
        ObjectMapper objectMapper = new ObjectMapper();
        String conditions = qb.getConditions();
        if (StringUtils.isBlank(conditions)) {
            return " ";
        }

        try {
            Object inputStr = objectMapper.readValue(conditions, Object.class);
            return where(inputStr);
        } catch (Exception e) {
            logger.error(e.getLocalizedMessage(), e);
        }
        return " ";
    }

    private String where(Object input) {
        StringBuilder sb = new StringBuilder();
        List<Object> list = (List<Object>) input;

        for (int i = 0; i < list.size(); i++) {
            List element = (List) list.get(i);
            //一纬数组
            if (!isArrays(element)) {
                //字段，操作，值
                if (element.size() == 3) {
                    String field = (String) element.get(0);
                    String operator = (String) element.get(1);
                    Object value = element.get(2);
                    sb.append("(").append(field);

                }
            }
        }
        return sb.toString();
    }

    /**
     * 是否是多维数组
     *
     * @param list
     * @return
     */
    private boolean isArrays(Object list) {
        int dimension = 1;
        if (list instanceof List) {
            List<?> input = (List<Object>) list;
            while (!input.isEmpty() && input.get(0) instanceof List<?>) {
                dimension++;
                if (dimension > 1) {
                    return true;
                }
                input = (List<?>) input.get(0);
            }
        }
        return false;
    }

    @Override
    public String exampleOrderBy(QueryBody qb) {
        return null;
    }
}
